﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using WebMinhHang.Libs;

namespace WebMinhHang
{
    public partial class _Default : System.Web.UI.Page
    {
        public string _DoanhSoBanBuon, _DoanhSoBanLe, _LaiBanBuon, _LaiBanLe;

        protected void Page_Load(object sender, EventArgs e)
        {
            lstDsDatabase.SelectedIndex = 0;
            try
            {
                if (!Page.IsPostBack)
                {
                    string dbName = lstDsDatabase.SelectedValue;
                    Connection.ConnectString(dbName);

                    cmbKyBaoCao.SelectedIndex = 17;
                    dateNgayBatDau.Enabled = true;
                    dateNgayKetThuc.Enabled = true;
                    DateTime now = DateTime.Now;
                    dateNgayBatDau.Date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
                    dateNgayKetThuc.Date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                }
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }
        }

        protected void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                ThoiDiemBaoCao.ThietLapKhoangThoiGianBaoCao(cmbKyBaoCao.Text, cmbKyBaoCao, dateNgayBatDau, dateNgayKetThuc);
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }
        }

        protected void btnThongKe_Click(object sender, EventArgs e)
        {
            DsThongKeBanLe ds = new DsThongKeBanLe();

            //Get database name form listbox (value)
            string dbName = lstDsDatabase.SelectedValue;

            string connectionString = Connection.ConnectString(dbName);
            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = 1000 * 10;

                DateTime ngayBatDau = new DateTime();
                DateTime ngayKetThuc = new DateTime();
                ngayBatDau = dateNgayBatDau.Date;
                ngayKetThuc = dateNgayKetThuc.Date;

                if (chkShowDetail.Checked)
                {
                    //Load chi tiết bán buôn, bán lẻ
                    cmd.CommandText = "Select_Detail_KhoanMucThongKeBanLe";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@UserID", null);
                    cmd.Parameters.AddWithValue("@MayTinhID", null);
                    cmd.Parameters.AddWithValue("@ThoiDiemDau", ngayBatDau);
                    cmd.Parameters.AddWithValue("@ThoiDiemCuoi", ngayKetThuc);

                    ds.Select_Detail_RutGon.Clear();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(ds.Select_Detail_RutGon);

                    cmd.CommandText = string.Format("SELECT A.Ngay, D.Ma MaHangHoa, D.Ten TenHangHoa, E.Ten TenNhomHangHoa, C.Value1 SoLuong,  \n" +
                                                    "		C.Value12 GiaBia, C.Value8 ChietKhau, C.Value6 GiaThanhToan, C.Value7 ThanhTien \n" +
                                                    "FROM LoaiChungTu LCT INNER JOIN ChungTu A ON LCT.ID = A.LoaiChungTuID  \n" +
                                                    "	INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                                    "	INNER JOIN  KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID  \n" +
                                                    "	INNER JOIN VatTuHangHoa D ON C.VatTuHangHoa1ID = D.ID  \n" +
                                                    "	INNER JOIN NhomVatTuHangHoa E ON D.NhomVatTuHangHoaID = E.ID  \n" +
                                                    "WHERE LCT.Ma IN('HoaDonBanHang') -- Bán buôn \n" +
                                                    "    AND C.Value5 IS NOT NULL AND C.Value3 IS NOT NULL \n" +
                                                    "	 AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n");

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@ThoiDiemDau", ngayBatDau);
                    cmd.Parameters.AddWithValue("@ThoiDiemCuoi", ngayKetThuc);

                    da = new SqlDataAdapter(cmd);
                    ds.Select_Detail_RutGonBanBuon.Clear();
                    da.Fill(ds.Select_Detail_RutGonBanBuon);

                    //Binding to grid. Nếu tích vào chọn hiển thị chi tiết
                    gridViewBanLe.DataSource = ds.Select_Detail_RutGon;
                    gridViewBanLe.DataBind();
                    GridViewBanBuon.DataSource = ds.Select_Detail_RutGonBanBuon;
                    GridViewBanBuon.DataBind();
                }
                else
                {
                    gridViewBanLe.DataSource = null;
                    gridViewBanLe.DataBind();
                    GridViewBanBuon.DataSource = null;
                    GridViewBanBuon.DataBind();
                }

                //Tính doanh số, lãi bán buôn bán lẻ
                decimal doanhSoBanBuon = 0, doanhSoBanLe = 0, laiBanBuon = 0, laiBanLe = 0;
                #region Tách doanh số, lãi bán buôn và bán lẻ - kỳ này
                //bán buôn
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBanBuon, SUM(Value7 - Value3) AS LaiBanBuon\n" +
                                      "FROM KhoanMucChungTu \n" +
                                      "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                      "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                      "											FROM ChungTu \n" +
                                      "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                      "																	WHERE Ma IN (N'HoaDonBanHang') \n" +
                                      "																	) \n" +
                                      "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi \n" +
                                      "											)\n" +
                                      "                   )\n" +
                                      " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", ngayBatDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", ngayKetThuc);

                SqlDataReader dr = null;
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoBanBuon"] != DBNull.Value)
                        {
                            doanhSoBanBuon = (decimal)dr["DoanhSoBanBuon"];
                        }
                        if (dr["LaiBanBuon"] != DBNull.Value)
                        {
                            laiBanBuon = (decimal)dr["LaiBanBuon"];
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();

                //bán lẻ
                cmd.CommandText = "SELECT SUM(Value7) AS DoanhSoBanLe,  SUM(Value7 - Value3) AS LaiBanLe\n" +
                                      "FROM KhoanMucChungTu \n" +
                                      "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                      "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                      "											FROM ChungTu \n" +
                                      "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                      "																	WHERE Ma IN (N'HoaDonBanLe') \n" +
                                      "																	) \n" +
                                      "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi \n" +
                                      "											)\n" +
                                      "                   )\n" +
                                      " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", ngayBatDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", ngayKetThuc);

                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoBanLe"] != DBNull.Value)
                        {
                            doanhSoBanLe = (decimal)dr["DoanhSoBanLe"];
                        }
                        if (dr["LaiBanLe"] != DBNull.Value)
                        {
                            laiBanLe = (decimal)dr["LaiBanLe"];
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                _DoanhSoBanBuon = String.Format("{0:### ### ### ###}", doanhSoBanBuon);
                _LaiBanBuon = String.Format("{0:### ### ### ###}", laiBanBuon);
                _DoanhSoBanLe = String.Format("{0:### ### ### ###}", doanhSoBanLe);
                _LaiBanLe = String.Format("{0:### ### ### ###}", laiBanLe);
            }
            catch (Exception ex)
            {
                string display = ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Thông báo", "alert('" + display + "');", true);
            }
            finally
            {
                con.Close();
            }
        }

        protected void lstDsDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            string dbName = lstDsDatabase.SelectedValue;
            Connection.ConnectString(dbName);

            gridViewBanLe.DataSource = null;
            gridViewBanLe.DataBind();
            GridViewBanBuon.DataSource = null;
            GridViewBanBuon.DataBind();
        }
    }
}
